/****** Object: View [dbo].[vw_4_11_4]   Script Date: 2014-01-19 21:47:09 ******/
USE [SHMIS];
GO
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_4_11_4]'))
BEGIN
DROP VIEW [dbo].[vw_4_11_4];
END
GO

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE VIEW [dbo].[vw_4_11_4]
AS
SELECT amount01,
       amount02,
       amount04,
       amount05,
       amount06,
       amount07,
       amount08,
       amount09,
         amount01
       + amount02
       + amount04
       + amount05
       + amount06
       + amount07
       + amount08
       + amount09
          AS Total_AmountSH,
       amount03,
       amount10,
       amount11,
       amount12,
       amount13,
       amount14,
       amount15,
         amount03
       + amount10
       + amount11
       + amount12
       + amount13
       + amount14
       + amount15
          AS Total_AmountSM,
         amount01
       + amount02
       + amount04
       + amount05
       + amount06
       + amount07
       + amount08
       + amount09
       + amount03
       + amount10
       + amount11
       + amount12
       + amount13
       + amount14
       + amount15
          AS total_amount,
       Year,
       Month
  FROM (SELECT sum (isnull (amount01, 0)) AS amount01,
               sum (isnull (amount02, 0)) AS amount02,
               sum (isnull (amount04, 0)) AS amount04,
               sum (isnull (amount05, 0)) AS amount05,
               sum (isnull (amount06, 0)) AS amount06,
               sum (isnull (amount07, 0)) AS amount07,
               sum (isnull (amount08, 0)) AS amount08,
               sum (isnull (amount09, 0)) AS amount09,
               sum (isnull (amount03, 0)) AS amount03,
               sum (isnull (amount10, 0)) AS amount10,
               sum (isnull (amount11, 0)) AS amount11,
               sum (isnull (amount12, 0)) AS amount12,
               sum (isnull (amount13, 0)) AS amount13,
               sum (isnull (amount14, 0)) AS amount14,
               sum (isnull (amount15, 0)) AS amount15,
               Year,
               Month
          FROM (SELECT Year,
                       Month,
                       CASE Amount_Type WHEN '01' THEN Amount END AS amount01,
                       CASE Amount_Type WHEN '02' THEN Amount END AS amount02,
                       CASE Amount_Type WHEN '04' THEN Amount END AS amount04,
                       CASE Amount_Type WHEN '05' THEN Amount END AS amount05,
                       CASE Amount_Type WHEN '06' THEN Amount END AS amount06,
                       CASE Amount_Type WHEN '07' THEN Amount END AS amount07,
                       CASE Amount_Type WHEN '08' THEN Amount END AS amount08,
                       CASE Amount_Type WHEN '09' THEN Amount END AS amount09,
                       CASE Amount_Type WHEN '03' THEN Amount END AS amount03,
                       CASE Amount_Type WHEN '10' THEN Amount END AS amount10,
                       CASE Amount_Type WHEN '11' THEN Amount END AS amount11,
                       CASE Amount_Type WHEN '12' THEN Amount END AS amount12,
                       CASE Amount_Type WHEN '13' THEN Amount END AS amount13,
                       CASE Amount_Type WHEN '14' THEN Amount END AS amount14,
                       CASE Amount_Type WHEN '15' THEN Amount END AS amount15
                  FROM HD_RealIncome
                 WHERE     deleted = '0'
                       AND verified = '1'
                       AND customer_simple_code IN
                              (SELECT c.RetValue
                                 FROM f_SplitStr (
                                         (SELECT param_val
                                            FROM SM_SysParams
                                           WHERE     paramgroup_id = '000'
                                                 AND param_id = '0008'),
                                         ',') c)) a
        GROUP BY Year, Month) b
GO

